using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;
using System.Text;

namespace PCSComProduction.WorkOrder.DS
{

	public class PRO_WorkOrderDetailDS ////: IObjectDS 
	{
		public PRO_WorkOrderDetailDS()
		{
		}
		private const string THIS = "PCSComProduction.WorkOrder.DS.PRO_WorkOrderDetailDS";
		private const string WOD = " WorkOrderDetail";
		private const string WOM = " WorkOrderMaster";
		private const string PRO = " Product";
		private const string UM = " UnitOfMeasure ";
		private const string OPENQUANTITY = " OpenQuantity ";
		private const string PARTNUMBER = " PartNumber ";
		private const string PARTNAME = " PartName ";
		private const string CAPTION_UM = " UM ";
		private const string MODEL = " Model ";
		private const string SELECTED = " Selected";
		public void Add(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";
			 
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS =null;
			try
			{
				PRO_WorkOrderDetailVO objObject = (PRO_WorkOrderDetailVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);
				
				strSql=	"INSERT INTO PRO_WorkOrderDetail("
					+ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.LINE_FLD + ","
					+ PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + ","
					+ PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.DUEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.STARTDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.PRODUCTID_FLD + ","
					+ PRO_WorkOrderDetailTable.PRIORITY_FLD + ","
					+ PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.AGC_FLD + ","
					+ PRO_WorkOrderDetailTable.ESTCST_FLD + ","
					+ PRO_WorkOrderDetailTable.STOCKUMID_FLD + ","
					+ PRO_WorkOrderDetailTable.STATUS_FLD + ","
					+ PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD + ")"
					+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD].Value = objObject.WorkOrderDetailID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.LINE_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.LINE_FLD].Value = objObject.Line;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD].Value = objObject.OrderQuantity;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD].Value = objObject.MfgCloseDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.DUEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.DUEDATE_FLD].Value = objObject.DueDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.STARTDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.STARTDATE_FLD].Value = objObject.StartDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.PRIORITY_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.PRIORITY_FLD].Value = objObject.Priority;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD].Value = objObject.SaleOrderDetailID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD].Value = objObject.WorkOrderMasterID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.AGC_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.AGC_FLD].Value = objObject.AGC;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.ESTCST_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.ESTCST_FLD].Value = objObject.EstCst;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.STOCKUMID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.STOCKUMID_FLD].Value = objObject.StockUMID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.STATUS_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.STATUS_FLD].Value = objObject.Status;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD].Value = objObject.FinCloseDate;


				
				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	

			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
	
		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			string strSql = String.Empty;
			strSql=	"DELETE " + PRO_WorkOrderDetailTable.TABLE_NAME + " WHERE  " + "SaleOrderMasterID" + "=" + pintID.ToString();
			OleDbConnection oconPCS=null;
			OleDbCommand ocmdPCS =null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
				ocmdPCS = null;

			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
																   
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}



			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
	
		public object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT "
					+ PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.LINE_FLD + ","
					+ PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + ","
					+ PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.DUEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.STARTDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.PRODUCTID_FLD + ","
					+ PRO_WorkOrderDetailTable.PRIORITY_FLD + ","
					+ PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.AGC_FLD + ","
					+ PRO_WorkOrderDetailTable.ESTCST_FLD + ","
					+ PRO_WorkOrderDetailTable.STOCKUMID_FLD + ","
					+ PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.STATUS_FLD
					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME
					+" WHERE " + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				PRO_WorkOrderDetailVO objObject = new PRO_WorkOrderDetailVO();

				while (odrPCS.Read())
				{ 
					if (odrPCS[PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD].ToString().Trim() != string.Empty)
					{
						objObject.SaleOrderMasterID = int.Parse(odrPCS[PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD].ToString().Trim());
					}
					objObject.WorkOrderDetailID = int.Parse(odrPCS[PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD].ToString().Trim());
					objObject.Line = int.Parse(odrPCS[PRO_WorkOrderDetailTable.LINE_FLD].ToString().Trim());
					objObject.OrderQuantity = Decimal.Parse(odrPCS[PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD].ToString().Trim());
					if (odrPCS[PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD].ToString().Trim() != string.Empty)
					{
						objObject.MfgCloseDate = DateTime.Parse(odrPCS[PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD].ToString().Trim());
					}
					objObject.DueDate = DateTime.Parse(odrPCS[PRO_WorkOrderDetailTable.DUEDATE_FLD].ToString().Trim());
					objObject.StartDate = DateTime.Parse(odrPCS[PRO_WorkOrderDetailTable.STARTDATE_FLD].ToString().Trim());
					objObject.ProductID = int.Parse(odrPCS[PRO_WorkOrderDetailTable.PRODUCTID_FLD].ToString().Trim());
					if (odrPCS[PRO_WorkOrderDetailTable.PRIORITY_FLD].ToString().Trim() != string.Empty)
					{
						objObject.Priority = int.Parse(odrPCS[PRO_WorkOrderDetailTable.PRIORITY_FLD].ToString().Trim());
					}
					if (odrPCS[PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD].ToString().Trim() != string.Empty)
					{
						objObject.SaleOrderDetailID = int.Parse(odrPCS[PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD].ToString().Trim());
					}
					objObject.WorkOrderMasterID = int.Parse(odrPCS[PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD].ToString().Trim());
					if (odrPCS[PRO_WorkOrderDetailTable.AGC_FLD].ToString().Trim() != string.Empty)
					{
						objObject.AGC = Decimal.Parse(odrPCS[PRO_WorkOrderDetailTable.AGC_FLD].ToString().Trim());
					}
					if (odrPCS[PRO_WorkOrderDetailTable.ESTCST_FLD].ToString().Trim() != string.Empty)
					{
						objObject.EstCst = Decimal.Parse(odrPCS[PRO_WorkOrderDetailTable.ESTCST_FLD].ToString().Trim());
					}
					objObject.StockUMID = int.Parse(odrPCS[PRO_WorkOrderDetailTable.STOCKUMID_FLD].ToString().Trim());
					if (odrPCS[PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD].ToString().Trim() != string.Empty)
					{
						objObject.FinCloseDate = DateTime.Parse(odrPCS[PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD].ToString().Trim());
					}
					objObject.Status = odrPCS[PRO_WorkOrderDetailTable.STATUS_FLD].ToString().Trim();

				}		
				return objObject;					
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Update(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";
			PRO_WorkOrderDetailVO objObject = (PRO_WorkOrderDetailVO) pobjObjecVO;
			//prepare value for parameters
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql=	"UPDATE PRO_WorkOrderDetail SET "
					+ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.LINE_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.DUEDATE_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.STARTDATE_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.PRODUCTID_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.PRIORITY_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.AGC_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.ESTCST_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.STOCKUMID_FLD + "=   ?" + ","
					+ PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD + "=  ?,"
					+ PRO_WorkOrderDetailTable.STATUS_FLD + "=  ?"
					+" WHERE " + PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD].Value = objObject.WorkOrderDetailID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.LINE_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.LINE_FLD].Value = objObject.Line;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD].Value = objObject.OrderQuantity;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD].Value = objObject.MfgCloseDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.DUEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.DUEDATE_FLD].Value = objObject.DueDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.STARTDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.STARTDATE_FLD].Value = objObject.StartDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.PRIORITY_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.PRIORITY_FLD].Value = objObject.Priority;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD].Value = objObject.SaleOrderDetailID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD].Value = objObject.WorkOrderMasterID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.AGC_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.AGC_FLD].Value = objObject.AGC;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.ESTCST_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.ESTCST_FLD].Value = objObject.EstCst;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.STOCKUMID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.STOCKUMID_FLD].Value = objObject.StockUMID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD].Value = objObject.FinCloseDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD].Value = objObject.SaleOrderMasterID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.STATUS_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.STATUS_FLD].Value = objObject.Status;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}

		}

		/// <summary>
		/// This method is used to search for all released work order line
		/// for selecting to issue materials
		/// </summary>
		/// <param name="phashCondition"></param>
		/// <returns></returns>
		public DataTable SearchWorkOrderToIssueMaterial(Hashtable phashCondition,string pstrRecordPermission)
		{
			const string METHOD_NAME = THIS + ".SearchWorkOrderToIssueMaterial()";
			const string REMAIN_COMPONENT_FOR_ISSUE_VIEW = "v_RemainComponentForWOIssueWithParentInfo";
						
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				strSql=	"SELECT Convert(bit, 0) as SELECTED, " + REMAIN_COMPONENT_FOR_ISSUE_VIEW + ".* "
					+ " FROM " + REMAIN_COMPONENT_FOR_ISSUE_VIEW
					+ " WHERE (1 = 1) \n" + pstrRecordPermission;
				
				if (phashCondition !=null)
				{
					IDictionaryEnumerator myEnumerator = phashCondition.GetEnumerator();
					while ( myEnumerator.MoveNext() )
					{
						strSql += " AND (" + myEnumerator.Key + "'" + myEnumerator.Value + "')";
					}
				}

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, REMAIN_COMPONENT_FOR_ISSUE_VIEW);

				return dstPCS.Tables[0];
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		/// <summary>
		/// This method is used to search for all released work order line
		/// for selecting to issue materials
		/// </summary>
		/// <returns></returns>
		public DataTable SearchWorkOrderToIssueMaterial(string pstrRecordPermission, int pintMasterLocationID, int pintLocationID,
			int pintWorkOrderMasterID, DateTime pdtmFromStartDate, DateTime pdtmToStartDate, int pintProductionLineID, int pintPurpose)
		{
			const string METHOD_NAME = THIS + ".SearchWorkOrderToIssueMaterial()";
			
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strViewName = "v_RemainComponentForWOIssueWithParentInfo";
				if (pintPurpose == (int)PurposeEnum.Compensation)
				{
				    strViewName = "v_RemainComponentForWOIssueWithParentInfoAll";
				}
				string strSql=	"SELECT Convert(bit, 0) as SELECTED, " + strViewName + ".* "
					+ " FROM " + strViewName
					+ " WHERE (1 = 1) \n" + pstrRecordPermission;
				if (pintMasterLocationID > 0)
					strSql += " AND MasterLocationID = " + pintMasterLocationID;
				// 28-04-2006 dungla: search by location
				if (pintLocationID > 0)
					strSql += " AND ProductionLineLocationID = " + pintLocationID;
				// 28-04-2006 dungla: search by location
				if (pintProductionLineID > 0)
					strSql += " AND ProductionLineID = " + pintProductionLineID;
				if (pintWorkOrderMasterID > 0)
					strSql += " AND WorkOrderMasterID = " + pintWorkOrderMasterID;
				if (pdtmFromStartDate > DateTime.MinValue)
					strSql += " AND StartDate >= ?";
				if (pdtmToStartDate > DateTime.MinValue)
					strSql += " AND StartDate <= ?";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				if (pdtmFromStartDate > DateTime.MinValue)
					ocmdPCS.Parameters.Add(new OleDbParameter("FromDate", OleDbType.Date)).Value = pdtmFromStartDate;
				if (pdtmToStartDate > DateTime.MinValue)
					ocmdPCS.Parameters.Add(new OleDbParameter("ToDate", OleDbType.Date)).Value = pdtmToStartDate;
				ocmdPCS.Connection.Open();
				//Debug.WriteLine(strSql);
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, strViewName);

				return dstPCS.Tables[0];
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataSet List()
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				strSql=	"SELECT "
					+ PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.LINE_FLD + ","
					+ PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + ","
					+ PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.DUEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.STARTDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.PRODUCTID_FLD + ","
					+ PRO_WorkOrderDetailTable.PRIORITY_FLD + ","
					+ PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.AGC_FLD + ","
					+ PRO_WorkOrderDetailTable.ESTCST_FLD + ","
					+ PRO_WorkOrderDetailTable.STOCKUMID_FLD + ","
					+ PRO_WorkOrderDetailTable.STATUS_FLD + ","
					+ PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD
					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WorkOrderDetailTable.TABLE_NAME);

				return dstPCS;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}


		}

		/// <summary>
		/// Gets detail by list of work order master
		/// </summary>
		/// <param name="pstrWOMasterID">List of WO Master</param>
		/// <returns></returns>
		public DataSet List(string pstrWOMasterID)
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				strSql=	"SELECT "
					+ PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.LINE_FLD + ","
					+ PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + ","
					+ PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.DUEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.STARTDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.PRODUCTID_FLD + ","
					+ PRO_WorkOrderDetailTable.PRIORITY_FLD + ","
					+ PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.AGC_FLD + ","
					+ PRO_WorkOrderDetailTable.ESTCST_FLD + ","
					+ PRO_WorkOrderDetailTable.STOCKUMID_FLD + ","
					+ PRO_WorkOrderDetailTable.STATUS_FLD + ","
					+ PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD
					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME
					+ " WHERE " + PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + " IN (" + pstrWOMasterID + ")";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WorkOrderDetailTable.TABLE_NAME);

				return dstPCS;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}


		}

		public DataSet List(string pstrWOMasterID, string pstrItems)
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				strSql=	"SELECT "
					+ PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.LINE_FLD + ","
					+ PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + ","
					+ PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.DUEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.STARTDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.PRODUCTID_FLD + ","
					+ PRO_WorkOrderDetailTable.PRIORITY_FLD + ","
					+ PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.AGC_FLD + ","
					+ PRO_WorkOrderDetailTable.ESTCST_FLD + ","
					+ PRO_WorkOrderDetailTable.STOCKUMID_FLD + ","
					+ PRO_WorkOrderDetailTable.STATUS_FLD + ","
					+ PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD
					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME
					+ " WHERE " + PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + " IN (" + pstrWOMasterID + ")"
					+ " AND " + PRO_WorkOrderDetailTable.PRODUCTID_FLD + " IN (" + pstrItems + ")"
					+ " AND " + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + " NOT IN"
					+ " (SELECT WorkOrderDetailID FROM PRO_WOBOM WHERE WorkOrderMasterID IN (" +  pstrWOMasterID + "))";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WorkOrderDetailTable.TABLE_NAME);

				return dstPCS;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}


		}

		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			string strSql;
			OleDbConnection oconPCS =null;
			OleDbCommandBuilder odcbPCS ;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				strSql=	"SELECT "
					+ PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.LINE_FLD + ","
					+ PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + ","
					+ PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.DUEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.STARTDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.PRODUCTID_FLD + ","
					+ PRO_WorkOrderDetailTable.PRIORITY_FLD + ","
					+ PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.AGC_FLD + ","
					+ PRO_WorkOrderDetailTable.ESTCST_FLD + ","
					+ PRO_WorkOrderDetailTable.STOCKUMID_FLD + ","
					+ PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.GROUP1_FLD + ","
					+ PRO_WorkOrderDetailTable.GROUP2_FLD + ","
					+ PRO_WorkOrderDetailTable.GROUPQUANTITY_FLD + ","
					+ PRO_WorkOrderDetailTable.PRODUCTIONLINEID_FLD + ","
					+ PRO_WorkOrderDetailTable.STATUS_FLD 
					+ "  FROM " + PRO_WorkOrderDetailTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData,PRO_WorkOrderDetailTable.TABLE_NAME);

			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
					{
																   
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
					}
					else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
					{

						throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
					}
					else
					{
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
					}
				}
				else{
					throw ex;
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}

		}
		/// <summary>
		/// - get all work order line by work order master id and return DataSet
		/// </summary>
		/// <param name="pintWOMasterID">WorkOrderMasterID</param>
		/// <returns>All Work Order Lines of a WorkOrderMaster</returns>
		public DataSet List(int pintWOMasterID)
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT SaleOrderMasterID, WorkOrderDetailID, Line, OrderQuantity, MfgCloseDate, DueDate,"
					+ " StartDate, ProductID, Priority, SaleOrderDetailID, WorkOrderMasterID, AGC, EstCst, StockUMID, Status,"
					+ " FinCloseDate, Group1, Group2, GroupQuantity, ProductionLineID"
					+ " FROM PRO_WorkOrderDetail WHERE WorkOrderMasterID=" + pintWOMasterID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				new OleDbDataAdapter(ocmdPCS).Fill(dstPCS, PRO_WorkOrderDetailTable.TABLE_NAME);
				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally
			{
				if ((oconPCS != null) && (oconPCS.State != ConnectionState.Closed))
				{
					oconPCS.Close();
				}
			}

		}
		/// <summary>
		/// Delete all work order line of a work order master
		/// </summary>
		/// <param name="pintWOMasterID">WorkOrderMasterID</param>
		/// <author>Trada</author>
		/// <date>Thursday, September 21 2006</date>
		public void DeleteByWOMasterID(int pintWOMasterID)
		{
			const string METHOD_NAME = THIS + ".DeleteByWOMasterID()";
			string strSql = String.Empty;
			strSql=	"DELETE " + PRO_WorkOrderDetailTable.TABLE_NAME 
				+ " WHERE  " + PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + "=" + pintWOMasterID.ToString();
			strSql +=	"; DELETE " + PRO_WorkOrderMasterTable.TABLE_NAME + " WHERE  " + PRO_WorkOrderMasterTable.WORKORDERMASTERID_FLD + "=" + pintWOMasterID.ToString()
				+   "; UPDATE " + MTR_CPOTable.TABLE_NAME + " SET " + MTR_CPOTable.WOGENERATEDID_FLD + "= null WHERE " + MTR_CPOTable.WOGENERATEDID_FLD + "=" + pintWOMasterID; 

			OleDbConnection oconPCS=null;
			OleDbCommand ocmdPCS =null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

//				ocmdPCS.CommandTimeout = 1000;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
				ocmdPCS = null;

			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
						throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
					else
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
				else
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}	
		}
		/// <summary>
		/// - Search all work order line have status Released and have Due Date between FromDueDate and ToDueDate
		/// 
		/// SELECT	Master.WorkOrderNo, Detail.Line, (Detail.OrderQuantity - 
		/// 	(SELECT ISNULL(SUM(CompletedQuantity),0) FROM PRO_WorkOrderCompletion
		/// 	WHERE PRO_WorkOrderCompletion.WorkOrderMasterID = Detail.WorkOrderMasterID
		/// 	AND PRO_WorkOrderCompletion.WorkOrderDetailID = Detail.WorkOrderDetailID)
		/// 	- (SELECT ISNULL(SUM(ScrapQuantity),0) FROM PRO_AOScrapDetail
		/// 	WHERE PRO_AOScrapDetail.WorkOrderMasterID = Detail.WorkOrderMasterID
		/// 	AND PRO_AOScrapDetail.WorkOrderDetailID = Detail.WorkOrderDetailID))
		/// 	AS OpenQuantity, Product.Code, Unit.Code, Detail.StartDate, Detail.DueDate,
		/// 	Product.Description, Product.Revision
		/// FROM	PRO_WorkOrderMaster AS Master, PRO_WorkOrderDetail AS Detail,
		/// 	ITM_Product AS Product, MST_UnitOfMeasure AS Unit
		/// WHERE	Master.WorkOrderMasterID = Detail.WorkOrderMasterID
		/// 	AND Detail.ProductID = Product.ProductID
		/// 	AND Product.StockUMID = Unit.UnitOfMeasureID
		/// 	AND Master.CCNID = pintCCNID
		/// 	AND Master.MasterLocationID = pintMasterLocationID
		/// 	AND Detail.Status = (int)penumStatus
		/// if FromDueDate >= ToDueDate
		/// 	AND Detail.DuetDate > ? (pdtmFromDueDate)
		/// else
		/// 	AND Detail.DueDate > ? (pdtmFromDueDate) AND Detail.DueDate < ? (pdtmToDueDate)
		/// </summary>
		/// <param name="pintCCNID">CCN ID</param>
		/// <param name="pintMasterLocationID">Master Location ID</param>
		/// <param name="pdtmFromDueDate">From due date of work order line</param>
		/// <param name="pdtmToDueDate">To due date of work order line</param>
		/// <returns>All work order line have status is Released</returns>
		/// <author>Trada</author>
		/// <date>Thursday, June 16 2005</date>
		public DataSet SearchWOForClose(WOLineStatus penumStatus, int pintCCNID, int pintMasterLocationID, DateTime pdtmFromDueDate, DateTime pdtmToDueDate)
		{
			
			const string METHOD_NAME = THIS + ".SearchUnReleaseWO()";
			const string YYYYMMDD = "yyyyMMdd";
			//const string ONE = "1";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" SELECT ");
                strSql.Append("'' AS " + SELECTED + ",");
                strSql.Append(WOD + "." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ",");
                strSql.Append(WOM + "." + PRO_WorkOrderMasterTable.WORKORDERNO_FLD + ",");
                strSql.Append(WOD + "." + PRO_WorkOrderDetailTable.LINE_FLD + ",");
                strSql.Append(PRO + "." + ITM_ProductTable.CODE_FLD + " AS " + PARTNUMBER + ", ");
                strSql.Append(PRO + "." + ITM_ProductTable.DESCRIPTION_FLD + ",");
                strSql.Append(PRO + "." + ITM_ProductTable.REVISION_FLD + ",");
                strSql.Append(" CA." + ITM_CategoryTable.CODE_FLD + " " + ITM_CategoryTable.TABLE_NAME + ITM_CategoryTable.CODE_FLD + ",");
                strSql.Append(" (" + WOD + "." + PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + " - ");
                strSql.Append("(SELECT ISNULL(SUM(" + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + "),0)");
                strSql.Append(" FROM " + PRO_WorkOrderCompletionTable.TABLE_NAME);
                strSql.Append(" WHERE " + PRO_WorkOrderCompletionTable.TABLE_NAME + "." + PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD + " = " + WOD + "." + PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD);
                strSql.Append(" AND " + PRO_WorkOrderCompletionTable.TABLE_NAME + "." + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + " = " + WOD + "." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ")");
                strSql.Append(" - (SELECT ISNULL(SUM(" + PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD + "),0)");
                strSql.Append(" FROM " + PRO_AOScrapDetailTable.TABLE_NAME);
                strSql.Append(" WHERE " + PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.WORKORDERMASTERID_FLD + " = " + WOD + "." + PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD);
                strSql.Append(" AND " + PRO_AOScrapDetailTable.TABLE_NAME + "." + PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + " = " + WOD + "." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + "))");
                strSql.Append(" AS " + OPENQUANTITY + ",");
                strSql.Append(UM + "." + MST_UnitOfMeasureTable.CODE_FLD + " AS " + CAPTION_UM + ", ");
                strSql.Append(WOD + "." + PRO_WorkOrderDetailTable.STARTDATE_FLD + ",");
                strSql.Append(WOD + "." + PRO_WorkOrderDetailTable.DUEDATE_FLD);
                strSql.Append(" FROM PRO_WorkOrderMaster AS  WorkOrderMaster ");
                strSql.Append(" INNER JOIN PRO_WorkOrderDetail AS  WorkOrderDetail ON ");
                strSql.Append(" WorkOrderMaster.WorkOrderMasterID = WorkOrderDetail.WorkOrderMasterID ");
                strSql.Append(" INNER JOIN ITM_Product AS  Product ON Product.ProductID = WorkOrderDetail.ProductID ");
                strSql.Append(" LEFT JOIN MST_UnitOfMeasure AS  UnitOfMeasure ON UnitOfMeasure.UnitOfMeasureID = Product.StockUMID ");
                strSql.Append(" LEFT JOIN ITM_Category CA ON CA.CategoryID = Product.CategoryID ");
                strSql.Append(" WHERE ");
					strSql.Append( WOM + "." + PRO_WorkOrderMasterTable.CCNID_FLD + " = " + pintCCNID);
                    strSql.Append(" AND " + WOM + "." + PRO_WorkOrderMasterTable.MASTERLOCATIONID_FLD + " = " + pintMasterLocationID);
                    strSql.Append(" AND " + WOD + "." + PRO_WorkOrderDetailTable.STATUS_FLD + " = " + (int)penumStatus);

				// FromDueDate and ToDueDate are not null
				if ((pdtmFromDueDate > DateTime.MinValue) && (pdtmToDueDate > DateTime.MinValue))
				{
					if (pdtmFromDueDate > pdtmToDueDate)
					{
						strSql.Append(" AND DATEDIFF(dayofyear, " + WOD + "." + PRO_WorkOrderDetailTable.DUEDATE_FLD + ",'" + pdtmFromDueDate.ToString(YYYYMMDD) + "') <= 0 ");
					}
					else
					{
                        strSql.Append(" AND DATEDIFF(dayofyear, " + WOD + "." + PRO_WorkOrderDetailTable.DUEDATE_FLD + ",'" + pdtmFromDueDate.ToString(YYYYMMDD) + "') <= 0 "); 
						strSql.Append(" AND DATEDIFF(dayofyear, " + WOD + "." + PRO_WorkOrderDetailTable.DUEDATE_FLD + ",'" + pdtmToDueDate.ToString(YYYYMMDD) + "') >= 0 ");
					}	
				}
				// FromDueDate is null and ToDueDate is not null
				if ((pdtmFromDueDate == DateTime.MinValue) && (pdtmToDueDate > DateTime.MinValue))
				{
					strSql.Append( " AND DATEDIFF(dayofyear, " + WOD + "." + PRO_WorkOrderDetailTable.DUEDATE_FLD + ",'" + pdtmToDueDate.ToString(YYYYMMDD) + "') >= 0 ");
				}
				// FromDueDate is not null and ToDueDate is null
				if ((pdtmFromDueDate > DateTime.MinValue) && (pdtmToDueDate == DateTime.MinValue))
				{
					strSql.Append(" AND DATEDIFF(dayofyear, " + WOD + "." + PRO_WorkOrderDetailTable.DUEDATE_FLD + ",'" + pdtmFromDueDate.ToString(YYYYMMDD) + "') <= 0 ");
				}									
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WorkOrderDetailTable.TABLE_NAME);

				return dstPCS;
			
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		/// <summary>
		/// Update WorkOrderLine: 
		/// 	Status = (int)penumStatus
		/// 	MfgCloseDate = pdtmCloseDate
		/// where WorkOrderDetailID IN (pstrListOfIDs)
		/// </summary>
		/// <param name="pdtmCloseDate">Close Date</param>
		/// <param name="pstrListOfIDs">WorkOrderDetailID to be Manufacturing Close</param>
		/// <old-param name="pintworkorderdetailid">WorkOrderDetailID to be Manufacturing Close</old-param>
		/// <author>Trada</author>
		/// <date>Thursday, June 16 2005</date>
		public void CloseWorkOrderLines(WOLineStatus penumStatus, DateTime pdtmCloseDate, string pstrListOfIDs)
		{
			const string METHOD_NAME = THIS + ".CloseWorkOrderLines()";
			//prepare value for parameters
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = string.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql =	"UPDATE PRO_WorkOrderDetail SET "
					+ PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD + " = ?" + ","
					+ PRO_WorkOrderDetailTable.STATUS_FLD + " = ?"
					+" WHERE " + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + " IN " + pstrListOfIDs;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD].Value = pdtmCloseDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.STATUS_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.STATUS_FLD].Value = (int)penumStatus;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}

		}
		/// <summary>
		/// UpdateWOCompletion
		/// </summary>
		/// <param name="penumStatus"></param>
		/// <param name="pintWODetailID"></param>
		/// <author>Trada</author>
		/// <date>Monday, September 12 2005</date>
		public void UpdateWOCompletion(WOLineStatus penumStatus, int pintWODetailID)
		{
			const string METHOD_NAME = THIS + ".UpdateWOCompletion()";
			//prepare value for parameters
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = string.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql =	"UPDATE PRO_WorkOrderDetail SET "
					+ PRO_WorkOrderDetailTable.STATUS_FLD + " = ?"
					+" WHERE " + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + " = " + pintWODetailID.ToString();

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.STATUS_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.STATUS_FLD].Value = (int)penumStatus;


				ocmdPCS.CommandText = strSql;
//				ocmdPCS.CommandTimeout = 10000;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}

		}
		
		/// <summary>
		/// Search UnRelease WOs
		/// </summary>
		/// <author>Trada</author>
		/// <date>Friday, June 3 2005</date>
		/// <param name="pintCCNID"></param>
		/// <param name="pintMasLocID"></param>
		/// <param name="pstrWONo"></param>
		/// <param name="pdtmFromStartDate"></param>
		/// <param name="pdtmToStartDate"></param>
		/// <returns></returns>
		public DataSet SearchUnReleaseWO(int pintCCNID, int pintMasLocID, string pstrWONo, int pintProLineID, int pintSaleOrderDetailID, DateTime pdtmFromStartDate, DateTime pdtmToStartDate, int pintStatus)
		{
			const string METHOD_NAME = THIS + ".SearchUnReleaseWO()";
			const string YYYYMMDD = "yyyyMMdd";
			//const string ONE = "1";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = string.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				
				strSql = "SELECT DISTINCT " 
					+ " '' AS " + SELECTED + ", "
					+ "PRO_ProductionLine.Code ProductionLine, "
					+ WOM + "." + PRO_WorkOrderMasterTable.WORKORDERNO_FLD + ", "
					+ WOM + "." + PRO_WorkOrderMasterTable.CCNID_FLD + ", "
					+ WOM + "." + PRO_WorkOrderMasterTable.WORKORDERMASTERID_FLD + ", "
					+ WOM + "." + PRO_WorkOrderMasterTable.MASTERLOCATIONID_FLD + ", "
					+ WOD + "." + PRO_WorkOrderDetailTable.LINE_FLD + "," 
					+ WOD + "." + PRO_WorkOrderDetailTable.STATUS_FLD + "," 
					+ WOD + "." + PRO_WorkOrderDetailTable.USERID_FLD + "," 
					+ WOD + "." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + "," 
					+ WOD + "." + PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + "," 
					+ PRO + "." + ITM_ProductTable.CODE_FLD + PARTNUMBER + ", " 
					+ PRO + "." + ITM_ProductTable.PRODUCTID_FLD + ", " 
					+ PRO + "." + ITM_ProductTable.DESCRIPTION_FLD + PARTNAME + ", " 
					+ PRO + "." + ITM_ProductTable.REVISION_FLD + MODEL + ", " 
					+ UM + "." + MST_UnitOfMeasureTable.CODE_FLD + " UM " + ", " 
					+ WOD + "." + PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + ", " 
					+ WOD + "." + PRO_WorkOrderDetailTable.ESTCST_FLD + ", " 
					+ WOD + "." + PRO_WorkOrderDetailTable.STARTDATE_FLD + ", " 
					+ WOD + "." + PRO_WorkOrderDetailTable.DESCRIPTION_FLD + ", " 
					+ WOD + "." + PRO_WorkOrderDetailTable.DUEDATE_FLD 
					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME + " " + WOD 
					+ " INNER JOIN " +  PRO_WorkOrderMasterTable.TABLE_NAME + " " + WOM 
					+ " ON " + WOD + "." + PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + " = " 
					+ WOM + "." + PRO_WorkOrderMasterTable.WORKORDERMASTERID_FLD
					+ " INNER JOIN "  + ITM_ProductTable.TABLE_NAME + " " + PRO 
					+ " ON " + WOD + "." + PRO_WorkOrderDetailTable.PRODUCTID_FLD + " = " 
					+ PRO + "." + ITM_ProductTable.PRODUCTID_FLD
					+ " INNER JOIN " +  MST_UnitOfMeasureTable.TABLE_NAME + " " + UM 
					+ " ON " + WOD + "." + PRO_WorkOrderDetailTable.STOCKUMID_FLD + " = " 
					+ UM + "." + MST_UnitOfMeasureTable.UNITOFMEASUREID_FLD
					+ " Left Join PRO_ProductionLine on PRO_ProductionLine.ProductionLineID = " + WOD + ".ProductionLineID "
					+ " WHERE " +  WOM + "." + PRO_WorkOrderMasterTable.CCNID_FLD + " = " + pintCCNID
					+ " AND " + WOM + "." + PRO_WorkOrderMasterTable.MASTERLOCATIONID_FLD + " = " + pintMasLocID.ToString()
					+ " AND " + WOD + "." + PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + " = " + pintSaleOrderDetailID.ToString();
					if (pintStatus  == (int)WOLineStatus.Unreleased)
						strSql += " AND " + WOD + "." + PRO_WorkOrderDetailTable.STATUS_FLD + " = " + pintStatus.ToString();
					else
					{
						strSql += " AND " + WOD + "." + PRO_WorkOrderDetailTable.STATUS_FLD + " = " + pintStatus.ToString();						
						strSql += " AND " + WOD + "." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + " NOT IN (Select WorkOrderDetailID from PRO_WorkOrderCompletion)";
					}
					
				// WorkOrderNO
				if ( pstrWONo != string.Empty )
					strSql += " AND " + WOM + "." + PRO_WorkOrderMasterTable.WORKORDERNO_FLD + " = '" + pstrWONo + "'";
				// FromStartDate
				if (pdtmFromStartDate > DateTime.MinValue)
				{
					strSql += " AND " + WOD + "." + PRO_WorkOrderDetailTable.STARTDATE_FLD + " >= ?";
					ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.STARTDATE_FLD, OleDbType.Date));
					ocmdPCS.Parameters[PRO_WorkOrderDetailTable.STARTDATE_FLD].Value = pdtmFromStartDate;
				}
				// ToStartDate
				if (pdtmToStartDate > DateTime.MinValue)
				{
					strSql += " AND " + WOD+ "." + PRO_WorkOrderDetailTable.STARTDATE_FLD + " <= ?";
					ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.DUEDATE_FLD, OleDbType.Date));
					ocmdPCS.Parameters[PRO_WorkOrderDetailTable.DUEDATE_FLD].Value = pdtmToStartDate;
				}
				//ProLineID
				if (pintProLineID > 0)
					strSql += " AND PRO_ProductionLine.ProductionLineID = " + pintProLineID;
				//Order by
				strSql += " ORDER BY Productionline, WorkOrderNo, Line, StartDate, DueDate";
				ocmdPCS.Connection.Open();
				ocmdPCS.CommandText = strSql;
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WorkOrderDetailTable.TABLE_NAME);

				return dstPCS;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}

			
		}

		/// <summary>
		/// Get List of WODetails by WOMaster
		/// </summary>
		/// <param name="pintWOMasterID">Work Order Master ID</param>
		/// <author>
		/// Do Manh Tuan
		/// 07 - 06 - 2005
		/// </author>
		/// <returns>DataSet</returns>
		public DataSet GetWODetailByMaster(int pintWOMasterID)
		{
			const string METHOD_NAME = THIS + ".List()", UNRELEASE = "Chưa duyệt", RELEASED = "Đã duyệt", MFGCLOSE = "Đã hoàn thành", FINCLOSE = "FinClose", EXTEND = "String", UM = "UM",
					  SALEORDERCODE = "SaleOrderCode", CODE = "Code", ACT = "ACT", STD = "STD", AVG = "AVG";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				strSql=	"SELECT "
					+ PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.LINE_FLD + ","
					+ "( SELECT " + PRO_ProductionLineTable.CODE_FLD + " FROM " + PRO_ProductionLineTable.TABLE_NAME + " WHERE " + PRO_ProductionLineTable.PRODUCTIONLINEID_FLD + " = " + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.PRODUCTIONLINEID_FLD + ") as " + PRO_ProductionLineTable.TABLE_NAME + PRO_ProductionLineTable.CODE_FLD + ","
					+ PRO_WorkOrderDetailTable.PRODUCTIONLINEID_FLD + "," // added: dungla for WORouting
					+ "( SELECT " + ITM_ProductTable.CODE_FLD + " FROM " + ITM_ProductTable.TABLE_NAME + " WHERE " + ITM_ProductTable.PRODUCTID_FLD + " = " + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.PRODUCTID_FLD + ") as " + ITM_ProductTable.CODE_FLD + ","
					+ "( SELECT " + ITM_ProductTable.DESCRIPTION_FLD + " FROM " + ITM_ProductTable.TABLE_NAME + " WHERE " + ITM_ProductTable.PRODUCTID_FLD + " = " + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.PRODUCTID_FLD + ") as " + ITM_ProductTable.TABLE_NAME + ITM_ProductTable.DESCRIPTION_FLD + ","
					+ "( SELECT " + ITM_ProductTable.REVISION_FLD + " FROM " + ITM_ProductTable.TABLE_NAME + " WHERE " + ITM_ProductTable.PRODUCTID_FLD + " = " + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.PRODUCTID_FLD + ") as " + ITM_ProductTable.REVISION_FLD + ","

					//HACK: added by Tuan TQ. 23 May, 2006. Add more category column
					+ "( SELECT " + ITM_CategoryTable.TABLE_NAME + "." + ITM_CategoryTable.CODE_FLD + " FROM " + ITM_CategoryTable.TABLE_NAME
					+ " INNER JOIN " + ITM_ProductTable.TABLE_NAME + " ON " + ITM_CategoryTable.TABLE_NAME + "." + ITM_CategoryTable.CATEGORYID_FLD
					+ " = " + ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.CATEGORYID_FLD
					+ " WHERE " + ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.PRODUCTID_FLD + " = " + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.PRODUCTID_FLD 
					+ ") as " + ITM_CategoryTable.TABLE_NAME + ITM_CategoryTable.CODE_FLD + ","
					//End hack

					+ "( SELECT " + MST_UnitOfMeasureTable.CODE_FLD + " FROM " + MST_UnitOfMeasureTable.TABLE_NAME + " WHERE " + MST_UnitOfMeasureTable.UNITOFMEASUREID_FLD + " = " + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.STOCKUMID_FLD + ") as " + UM + ","
					+ PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + ","
					+ PRO_WorkOrderDetailTable.STATUS_FLD + ","
					+ " CASE " + PRO_WorkOrderDetailTable.STATUS_FLD
					+ " WHEN " +  (int) WOLineStatus.Unreleased + " THEN N'" + UNRELEASE
					+ "' WHEN " + (int) WOLineStatus.Released + " THEN N'" + RELEASED
					+ "' WHEN " + (int) WOLineStatus.MfgClose + " THEN N'" + MFGCLOSE
					+ "' WHEN " + (int) WOLineStatus.FinClose + " THEN N'" + FINCLOSE
					+ "' END" + " as " + PRO_WorkOrderDetailTable.STATUS_FLD + EXTEND + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.DESCRIPTION_FLD + ","
					+ PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.STARTDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.DUEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.PRODUCTID_FLD + ","
					+ PRO_WorkOrderDetailTable.PRIORITY_FLD + ","
					+ PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + ","
					+ "( SELECT " + SO_SaleOrderMasterTable.CODE_FLD + " FROM " + SO_SaleOrderMasterTable.TABLE_NAME + " WHERE " + SO_SaleOrderMasterTable.SALEORDERMASTERID_FLD + " = " + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD + ") as " + SALEORDERCODE + ","
					+ "( SELECT " + SO_SaleOrderDetailTable.FGOODSCODE_FLD + " FROM " + SO_SaleOrderDetailTable.TABLE_NAME + " WHERE " + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD + " = " + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + ") as " + SO_SaleOrderDetailTable.FGOODSCODE_FLD + ","
					+ "( SELECT " + ITM_ProductTable.COSTMETHOD_FLD + " = CASE " + ITM_ProductTable.COSTMETHOD_FLD + " WHEN " + (int) CostMethodEnum.ACT + " THEN '" + ACT + "' WHEN " + (int) CostMethodEnum.STD + " THEN '" + STD +  "' WHEN " + (int) CostMethodEnum.AVG + " THEN '" + AVG + "' END FROM " + ITM_ProductTable.TABLE_NAME + " WHERE " + ITM_ProductTable.PRODUCTID_FLD + " = " + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.PRODUCTID_FLD + ") as " + ITM_ProductTable.COSTMETHOD_FLD + ","
					+ PRO_WorkOrderDetailTable.AGC_FLD + ","
					+ PRO_WorkOrderDetailTable.ESTCST_FLD + ","
					+ "( SELECT " + MST_AGCTable.CODE_FLD + " FROM " + MST_AGCTable.TABLE_NAME + " WHERE " + MST_AGCTable.AGCID_FLD + " = " + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.AGC_FLD + ") as " + PRO_WorkOrderDetailTable.AGC_FLD + CODE + ","
					+ PRO_WorkOrderDetailTable.STOCKUMID_FLD + ","
					+ PRO_WorkOrderDetailTable.GROUP1_FLD + ","
					+ PRO_WorkOrderDetailTable.GROUP2_FLD + ","
					+ PRO_WorkOrderDetailTable.GROUPQUANTITY_FLD + ","
					+ PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD
					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME
					+ " WHERE " + PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + " = " + pintWOMasterID
					+ " ORDER BY Line";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WorkOrderDetailTable.TABLE_NAME);

				return dstPCS;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		/// <summary>
		/// Get StockUMCode, CostMethod, AGCCode
		/// </summary>
		/// <param name="pintProductID"></param>
		/// <returns></returns>
		public string GetProductInforByID(int pintProductID)
		{
			const string METHOD_NAME = THIS + ".GetProductInforByID()", ACT = "ACT", STD = "STD", AVG = "AVG";
		
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT "
					+ "( SELECT " + MST_UnitOfMeasureTable.CODE_FLD + " FROM " + MST_UnitOfMeasureTable.TABLE_NAME + " WHERE " 
					+ MST_UnitOfMeasureTable.UNITOFMEASUREID_FLD + " = " + ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.STOCKUMID_FLD + "),"
					+ "(SELECT " + ITM_ProductTable.COSTMETHOD_FLD + " = " + " CASE " + ITM_ProductTable.COSTMETHOD_FLD + 
					" WHEN " + (int) CostMethodEnum.ACT + " THEN '" + ACT + "' WHEN " + (int) CostMethodEnum.STD + " THEN '" + STD +  "' WHEN " + (int) CostMethodEnum.AVG + " THEN '" + AVG + "' END),"
					+ "(SELECT " + MST_AGCTable.CODE_FLD + " FROM " + MST_AGCTable.TABLE_NAME + " WHERE " + MST_AGCTable.AGCID_FLD + " = " 
					+ ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.AGCID_FLD + "),"
					+ "(SELECT " + ITM_CostTable.ITEMCOSTTOTALAMOUNT21_FLD + " FROM " + ITM_CostTable.TABLE_NAME + " WHERE " + ITM_CostTable.PRODUCTID_FLD + " = " + ITM_ProductTable.TABLE_NAME + "." + ITM_ProductTable.PRODUCTID_FLD + ")"
					+ " FROM " + ITM_ProductTable.TABLE_NAME
					+ " WHERE " + ITM_ProductTable.PRODUCTID_FLD + "=" + pintProductID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				while (odrPCS.Read())
				{ 
					return odrPCS[0].ToString() + ";" + odrPCS[1].ToString() + ";" + odrPCS[2].ToString() + ";" + odrPCS[3].ToString();
				}		
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
			return string.Empty;
		}

		/// <summary>
		/// Release WO
		/// </summary>
		/// <autho>Trada</autho>
		/// <date>Monday, June 6 2005</date>
		/// <param name="pData"></param>
		public void ReleaseWO(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".ReleaseWO()";
			OleDbConnection oconPCS =null;
			OleDbCommandBuilder odcbPCS ;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				string strSql=	"SELECT "
					+ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.STATUS_FLD + ","
					+ PRO_WorkOrderDetailTable.DESCRIPTION_FLD + ","
					+ PRO_WorkOrderDetailTable.USERID_FLD 
					+ "  FROM " + PRO_WorkOrderDetailTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData,PRO_WorkOrderDetailTable.TABLE_NAME);

			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
					else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
						throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
					else
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
				else
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
	
		/// <summary>
		/// SELECT OrderQuantity, WODetailID FROM PRO_WorkOrderDetail WHERE WODetailID = pintWODetailID
		/// </summary>
		public decimal GetOrderQuantityByWODetail(int pintWODetailID)
		{
			const string METHOD_NAME = THIS + ".GetScrapQuantity()";
					
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{

				string strSql = "SELECT ISNULL(" + PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + ", 0) "
					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME + " WHERE " 
					+ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + " = " + pintWODetailID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				return decimal.Parse(ocmdPCS.ExecuteScalar().ToString());
			}		
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}					
				}				
			}
		}

		/// <summary>
		/// This method is used to get some information from WO Line ID
		/// </summary>
		/// <param name="pintID"></param>
		/// <returns></returns>
		public object GetWorkOrderDetailInfo(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetWorkOrderDetailInfo()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT "
					+ PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.LINE_FLD + ","
					+ PRO_WorkOrderDetailTable.INCREMENT_FLD + ","
					+ PRO_WorkOrderDetailTable.DESCRIPTION_FLD + ","
					+ PRO_WorkOrderDetailTable.PRODUCTID_FLD + ","
					+ PRO_WorkOrderDetailTable.STATUS_FLD 
					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME
					+ " WHERE " + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				PRO_WorkOrderDetailVO objObject = new PRO_WorkOrderDetailVO();

				while (odrPCS.Read())
				{ 
					objObject.WorkOrderDetailID = int.Parse(odrPCS[PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD].ToString().Trim());
					objObject.Line = int.Parse(odrPCS[PRO_WorkOrderDetailTable.LINE_FLD].ToString().Trim());
					objObject.Status = odrPCS[PRO_WorkOrderDetailTable.STATUS_FLD].ToString().Trim();
					if (odrPCS[PRO_WorkOrderDetailTable.INCREMENT_FLD] != DBNull.Value)
					{
						objObject.Increment = int.Parse(odrPCS[PRO_WorkOrderDetailTable.INCREMENT_FLD].ToString().Trim());
					}
					else
						objObject.Increment = -1;
					objObject.Description = odrPCS[PRO_WorkOrderDetailTable.DESCRIPTION_FLD].ToString().Trim();
					objObject.ProductID = int.Parse(odrPCS[PRO_WorkOrderDetailTable.PRODUCTID_FLD].ToString());
				}		
				return objObject;					
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void UpdateWORouting(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".UpdateWORouting()";
			PRO_WorkOrderDetailVO objObject = (PRO_WorkOrderDetailVO) pobjObjecVO;
			//prepare value for parameters
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql=	"UPDATE PRO_WorkOrderDetail SET "
					+ PRO_WorkOrderDetailTable.INCREMENT_FLD + "=  ?,"
					+ PRO_WorkOrderDetailTable.DESCRIPTION_FLD + "=  ?"
					+" WHERE " + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.INCREMENT_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.INCREMENT_FLD].Value = objObject.Increment;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.DESCRIPTION_FLD, OleDbType.VarChar));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.DESCRIPTION_FLD].Value = objObject.Description;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD].Value = objObject.WorkOrderDetailID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
			}
			catch(OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
																   
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}

		}

		/// <summary>
		/// Get Status of WOLine
		/// </summary>
		/// <param name="pintID"></param>
		/// <returns></returns>
		/// <author>
		/// Do Manh Tuan
		/// 04-07-2005
		/// </author>
		public int GetStatusForWOLine(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetWorkOrderDetailInfo()";
			
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT "
					+ PRO_WorkOrderDetailTable.STATUS_FLD
					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME
					+ " WHERE " + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult != null)
				{
					return int.Parse(objResult.ToString());
				}
				else
				{
					return 0;
				}
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void AddAndReturnID(object pobjObjectVO)
		{
		}

		public DataTable GetBinAvailableByProductionLine(int pintProductionLineID)
		{
			const string METHOD_NAME = THIS + ".ListByWorkCenter()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = " select V_PRODUCT_AVAILABLE_IN_BIN_INCOMING.*"
							+	" from dbo.V_PRODUCT_AVAILABLE_IN_BIN_INCOMING"
							+   " Inner join PRO_ProductionLine on V_PRODUCT_AVAILABLE_IN_BIN_INCOMING.LocationID = PRO_ProductionLIne.LocationID "
							+   " where PRO_ProductionLine.ProductionLineID = " + pintProductionLineID;
				
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WorkOrderDetailTable.TABLE_NAME);

				return dstPCS.Tables[0];
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}

		}

		public DataTable GetUncompletedQuantity()
		{
			const string METHOD_NAME = THIS + ".GetUncompletedQuantity()";
			
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "SELECT B.ComponentID, ISNULL(SUM((D.OrderQuantity - ISNULL(CompletedQuantity,0)) * Quantity * ISNULL(P.Rate,1)),0) Quantity"
                    + " FROM PRO_WorkOrderDetail D JOIN PRO_WOBOM B ON D.WorkOrderDetailID = B.WorkOrderDetailID"
					+ " JOIN ITM_Product P ON B.ComponentID = P.ProductID"
					+ " LEFT JOIN PRO_WorkOrderCompletion C ON D.WorkOrderDetailID = C.WorkOrderDetailID"
					+ " WHERE D.Status = 2"
					+ " GROUP BY B.ComponentID ORDER BY B.ComponentID";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				DataTable dtbData = new DataTable(PRO_WorkOrderDetailTable.TABLE_NAME);
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbData);

				return dtbData;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}

		}

		public DataSet ListByWorkCenter(int pintWorkCenterID)
		{
			const string METHOD_NAME = THIS + ".ListByWorkCenter()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				strSql=	"SELECT DISTINCT "
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.SALEORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.LINE_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.MFGCLOSEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.DUEDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.STARTDATE_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.PRODUCTID_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.PRIORITY_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.WORKORDERMASTERID_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.AGC_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.ESTCST_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.STOCKUMID_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.STATUS_FLD + ","
					+ PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.FINCLOSEDATE_FLD + ","
					+ MST_WorkCenterTable.TABLE_NAME + "." + MST_WorkCenterTable.CODE_FLD + " WorkCenterCode,"
					
					+ " CASE "
					+ " WHEN " + PRO_WORoutingTable.TABLE_NAME + "." + PRO_WORoutingTable.PACER_FLD + "='M' THEN "
					+ PRO_WORoutingTable.MACHINERUNTIME_FLD + " + " + PRO_WORoutingTable.MACHINESETUPTIME_FLD + " + " + PRO_WORoutingTable.MOVETIME_FLD
					+ " WHEN " + PRO_WORoutingTable.TABLE_NAME + "." + PRO_WORoutingTable.PACER_FLD + "='L' THEN "
					+ PRO_WORoutingTable.LABORRUNTIME_FLD + " + " + PRO_WORoutingTable.LABORSETUPTIME_FLD + " + " + PRO_WORoutingTable.MOVETIME_FLD
					+ " WHEN " + PRO_WORoutingTable.TABLE_NAME + "." + PRO_WORoutingTable.PACER_FLD + "='B' THEN "
					+ PRO_WORoutingTable.MACHINERUNTIME_FLD + " + " + PRO_WORoutingTable.MACHINESETUPTIME_FLD + " + " + PRO_WORoutingTable.LABORRUNTIME_FLD + " + " + PRO_WORoutingTable.LABORSETUPTIME_FLD + " + " + PRO_WORoutingTable.MOVETIME_FLD
					+ " END LeadTime "

					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME
					+ " INNER JOIN " + PRO_WORoutingTable.TABLE_NAME
					+ " ON " + PRO_WorkOrderDetailTable.TABLE_NAME + "." + PRO_WorkOrderDetailTable.PRODUCTID_FLD + "=" + PRO_WORoutingTable.TABLE_NAME + "." + PRO_WORoutingTable.PRODUCTID_FLD
					+ " INNER JOIN " + MST_WorkCenterTable.TABLE_NAME
					+ " ON " + PRO_WORoutingTable.TABLE_NAME + "." + PRO_WORoutingTable.WORKCENTERID_FLD + "=" + MST_WorkCenterTable.TABLE_NAME + "." + MST_WorkCenterTable.WORKCENTERID_FLD + " AND " + MST_WorkCenterTable.ISMAIN_FLD + " = 1 "
					+ " WHERE " + MST_WorkCenterTable.TABLE_NAME + "." + MST_WorkCenterTable.WORKCENTERID_FLD + " = " + pintWorkCenterID.ToString();
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WorkOrderDetailTable.TABLE_NAME);

				return dstPCS;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}

		}
		public bool IsConverted(int pintSaleOrderDetailID)
		{
			const string METHOD_NAME = THIS + ".IsConverted()";
					
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{

				string strSql = "SELECT COUNT(" + PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + ")"
					+ " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME
					+ " WHERE " + PRO_WorkOrderDetailTable.SALEORDERDETAILID_FLD + "=" + pintSaleOrderDetailID;
				
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				object objResult = ocmdPCS.ExecuteScalar();
				try
				{
					return Convert.ToInt32(objResult) > 0;
				}
				catch
				{
					return false;
				}
			}		
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}					
				}				
			}
		}
		/// <summary>
		/// Check work order line is unreleaseable
		/// </summary>
		/// <param name="pstrWorkOrderDetailID">List of work order to check</param>
		/// <param name="pstrSaleOrderDetailID">List of sale order to check</param>
		/// <returns>true: unreleaseable | false if not</returns>
		public bool IsUnreleaseable(string pstrWorkOrderDetailID, string pstrSaleOrderDetailID)
		{
			const string METHOD_NAME = THIS + ".IsUnreleaseable()";
					
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = " SELECT SaleOrderDetailID FROM PRO_IssueSubMaterialMaster"
					+ " WHERE SaleOrderDetailID IN (" + pstrSaleOrderDetailID + ");"

					+ " SELECT WorkOrderDetailID FROM PRO_IssueMaterialDetail"
					+ " WHERE WorkOrderDetailID IN (" + pstrWorkOrderDetailID + ");"

					+ " SELECT WorkOrderDetailID FROM PRO_WorkOrderCompletion"
					+ " WHERE WorkOrderDetailID IN (" + pstrWorkOrderDetailID + ");"

					+ " SELECT WorkOrderDetailID FROM PRO_RequiredMaterialDetail"
					+ " WHERE WorkOrderDetailID IN (" + pstrWorkOrderDetailID + ");";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				DataSet dstData = new DataSet();
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstData);
				bool blnResult = true;
				foreach (DataTable dtbData in dstData.Tables)
				{
					if (dtbData.Rows.Count > 0)
					{
						blnResult = false;
						break;
					}
				}
				return blnResult;
			}		
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}					
				}				
			}
		}
	}
}
